﻿using SIIE_ERP.Codigo;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIIE_ERP.BaseDatos.Configuracion.Empresa
{
    public class dbo_Trans_Presupuesto_Venta
    {
        #region Propiedades

        private string _RFC;
        private string _Usuario;

        #endregion

        #region Constructores

        public dbo_Trans_Presupuesto_Venta(string RFC, string Usuario)
        {
            this._RFC = RFC;
            this._Usuario = Usuario;
        }

        #endregion

        #region INSERT

        public Respuesta Insert_Presupuesto_Ventas(

            int Anio,
            double M1,
            double M2,
            double M3,
            double M4,
            double M5,
            double M6,
            double M7,
            double M8,
            double M9,
            double M10,
            double M11,
            double M12,
            int Id_Moneda
        )
        {
            try
            {
                string Query = @"INSERT INTO dbo_Trans_Presupuesto_Venta
                             (
                                	Id_Presupuesto_Venta,
	                                RFC,
	                                Anio,
	                                M1,
	                                M2,
	                                M3,
	                                M4,
	                                M5,
	                                M6,
	                                M7,
	                                M8,
	                                M9,
	                                M10,
	                                M11,
	                                M12,
	                                Id_Moneda,
	                                Fecha_Alta,
	                                Usuario_Alta,
	                                Fecha_Mantto,
	                                Usuario_Mantto
                             )
                             VALUES
                             (
                                IIF(DCount('*', 'dbo_Trans_Presupuesto_Venta') = 0, 1, DMax('Id_Presupuesto_Venta', 'dbo_Trans_Presupuesto_Venta') + 1),
                                '@@RFC',
	                            @@Anio,
	                            @@M1,
	                            @@M2,
	                            @@M3,
	                            @@M4,
	                            @@M5,
	                            @@M6,
	                            @@M7,
	                            @@M8,
	                            @@M9,
	                            @@M10,
	                            @@M11,
	                            @@M12,
	                            @@Id_Moneda,                             
                                NOW(),
                                '@@Usuario_Alta',
                                NOW(),
                                '@@Usuario_Mantto'
                             )";

                Query = Query.Replace("@@RFC", this._RFC);

                Query = Query.Replace("@@M10", M10.ToString());
                Query = Query.Replace("@@M11", M11.ToString());
                Query = Query.Replace("@@M12", M12.ToString());

                Query = Query.Replace("@@Anio", Anio.ToString());
                Query = Query.Replace("@@M1", M1.ToString());
                Query = Query.Replace("@@M2", M2.ToString());
                Query = Query.Replace("@@M3", M3.ToString());
                Query = Query.Replace("@@M4", M4.ToString());
                Query = Query.Replace("@@M5", M5.ToString());
                Query = Query.Replace("@@M6", M6.ToString());
                Query = Query.Replace("@@M7", M7.ToString());
                Query = Query.Replace("@@M8", M8.ToString());
                Query = Query.Replace("@@M9", M9.ToString());
                Query = Query.Replace("@@Id_Moneda", Id_Moneda.ToString());

                Query = Query.Replace("@@Usuario_Alta", this._Usuario);
                Query = Query.Replace("@@Usuario_Mantto", this._Usuario);

                return SrvDB.ExecuteQuery_Insert(Query);


            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        #endregion

        #region UPDATE

        public Respuesta Update_Presupuesto_Ventas_Por_Id(
            object Id,
            int Anio,
            double M1,
            double M2,
            double M3,
            double M4,
            double M5,
            double M6,
            double M7,
            double M8,
            double M9,
            double M10,
            double M11,
            double M12,
            int Id_Moneda
        )
        {
            try
            {
                string Query = @"UPDATE dbo_Trans_Presupuesto_Venta SET
                                             
                                    Anio = @@Anio,
	                                M1 = @@M1,
	                                M2 = @@M2,
	                                M3 = @@M3,
	                                M4 = @@M4,
	                                M5 = @@M5,
	                                M6 = @@M6,
	                                M7 = @@M7,
	                                M8 = @@M8,
	                                M9 = @@M9,
	                                M10 = @@M10,
	                                M11 = @@M11,
	                                M12 = @@M12,
	                                Id_Moneda = @@Id_Moneda,                 
                                    Fecha_Mantto = NOW(),
                                    Usuario_Mantto = '@@Usuario_Mantto'

                                 WHERE Id_Presupuesto_Venta = @@Id";

                Query = Query.Replace("@@Anio", Anio.ToString());

                Query = Query.Replace("@@M10", M10.ToString());
                Query = Query.Replace("@@M11", M11.ToString());
                Query = Query.Replace("@@M12", M12.ToString());
                Query = Query.Replace("@@M1", M1.ToString());
                Query = Query.Replace("@@M2", M2.ToString());
                Query = Query.Replace("@@M3", M3.ToString());
                Query = Query.Replace("@@M4", M4.ToString());
                Query = Query.Replace("@@M5", M5.ToString());
                Query = Query.Replace("@@M6", M6.ToString());
                Query = Query.Replace("@@M7", M7.ToString());
                Query = Query.Replace("@@M8", M8.ToString());
                Query = Query.Replace("@@M9", M9.ToString());
                Query = Query.Replace("@@Id_Moneda", Id_Moneda.ToString());

                Query = Query.Replace("@@Usuario_Mantto", this._Usuario);
                Query = Query.Replace("@@Id", ((Int32)Id).ToString());

                return SrvDB.ExecuteQuery_Update(Query);


            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        #endregion

        #region SELECT

        public DataTable Get_Presupuesto_Ventas_Por_Id(object Id)
        {

            string Query = @"SELECT

                                A1.*,
								A2.Nombre AS Desc_Moneda
								
                            FROM (dbo_Trans_Presupuesto_Venta AS A1
                            
                                INNER JOIN dbo_Cat_Moneda       AS A2 ON A1.Id_Moneda = A2.Id_Moneda)
                                
                            WHERE A1.Id_Presupuesto_Venta = @@Id";

            Query = Query.Replace("@@Id", ((Int32)Id).ToString());

            return SrvDB.ExecuteQuery_Get(Query);
        }

        public DataTable Get_Presupuesto_Ventas_Por_Nombre(string Nombre)
        {

            string Query = @"
                            SELECT

                                A1.*,
								A2.Nombre AS Desc_Moneda
								
                            FROM (dbo_Trans_Presupuesto_Venta AS A1
                            
                                INNER JOIN dbo_Cat_Moneda       AS A2 ON A1.Id_Moneda = A2.Id_Moneda)
                                
                            WHERE A1.Nombre = '@@Nombre' AND A1.RFC = '@@RFC'";

            Query = Query.Replace("@@Nombre", Nombre.Replace("'", "''"));
            Query = Query.Replace("@@RFC", this._RFC);

            return SrvDB.ExecuteQuery_Get(Query);
        }

        public DataTable Get_Presupuesto_Ventas()
        {
            string Query = @"SELECT

                                A1.*,
								A2.Nombre AS Desc_Moneda
								
                            FROM (dbo_Trans_Presupuesto_Venta AS A1
                            
                                INNER JOIN dbo_Cat_Moneda       AS A2 ON A1.Id_Moneda = A2.Id_Moneda)
                                
                            WHERE A1.RFC = '@@RFC'";

            Query = Query.Replace("@@RFC", this._RFC);

            return SrvDB.ExecuteQuery_Get(Query);
        }


        public DataTable Get_Presupuesto_Ventas(int Tiempo)
        {
            string Query = @"SELECT

                                A1.*,
								A2.Nombre AS Desc_Moneda
								
                            FROM (dbo_Trans_Presupuesto_Venta AS A1
                            
                                INNER JOIN dbo_Cat_Moneda       AS A2 ON A1.Id_Moneda = A2.Id_Moneda)
                                
                            WHERE A1.RFC = '@@RFC'
                                
                            AND 

                            (
					        (@@Tiempo = 1 and A1.Anio = Year(NOW())) or
					        (@@Tiempo = 2 and A1.Anio = Year(NOW())) or
					        (@@Tiempo = 3 and A1.Anio = Year(NOW())) or
					        (@@Tiempo = 4 and A1.Anio = Year(NOW())) or
					        (@@Tiempo = 5 and A1.Anio = Year(DateAdd('yyyy',-1,NOW())))
					        )
                    ";

            Query = Query.Replace("@@RFC", this._RFC);
            Query = Query.Replace("@@Tiempo", Tiempo.ToString());

            return SrvDB.ExecuteQuery_Get(Query);
        }


        #endregion

        #region delete
        public Respuesta Delete_PresupuestoVentas_Por_Id(
          object Id
      )
        {
            try
            {
                string Query = @"Delete from dbo_Trans_Presupuesto_Venta
                Where  Id_Presupuesto_Venta = @@Id";

                Query = Query.Replace("@@Id", ((Int32)Id).ToString());

                return SrvDB.ExecuteQuery_Delete(Query);


            }
            catch (Exception Ex)
            {
                return new Respuesta(2, Ex.Message);
            }
        }

        #endregion
    }
}
